package com.pb.infra.oth.util;

import com.pb.infra.oth.entity.common.Result;
import com.pb.infra.oth.entity.excel.ImportSheet;
import org.apache.poi.ss.usermodel.*;

import java.io.InputStream;
import java.util.List;

/**
 * 通用导入
 *
 * @author liudebao
 * @date 2020/1/14
 */
public class ImportExcelUtil {
    private ImportExcelUtil() {
        throw new IllegalStateException("Utility class");
    }

    /**
     * 导入校验
     *
     * @param input
     * @param sheets
     * @return
     * @throws Exception
     */
    public static Result check(InputStream input, List<ImportSheet> sheets) throws Exception {
        StringBuilder error = new StringBuilder();
        error.append("**********<span style='color:red;font-weight:bold;'>导入失败</span>**********");
        StringBuilder sheetError;
        StringBuilder rowError;
        boolean hasError = false;
        boolean hasSheetError = false;
        boolean hasRowError = false;
        Workbook workbook = WorkbookFactory.create(input);
        Sheet sheet;
        Row row;
        Cell cell;
        for (int i = 0; i < sheets.size(); i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet != null) {
                sheetError = new StringBuilder();
                if (sheets.size() > 1) {
                    sheetError.append("<br/>" + sheet.getSheetName() + "：");
                }
                for (int j = sheets.get(i).getStartRowIndex(); j < sheet.getPhysicalNumberOfRows(); j++) {
                    row = sheet.getRow(j);
                    if (row != null) {
                        rowError = new StringBuilder();
                        rowError.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;第 " + (j + 1) + " 行：");
                        for (int k = 0; k < sheets.get(i).getColumn().length; k++) {
                            cell = row.getCell(k);
                            if (cell == null && !sheets.get(i).getColumn()[k].getNullable()) {
                                hasRowError = true;
                                rowError.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第 " + (k + 1) + " 列不能为空");
                            } else {
                                if (cell != null) {
                                    if (!sheets.get(i).getColumn()[k].getNullable() && StringUtil.isEmpty(cell.toString().trim())) {
                                        hasRowError = true;
                                        rowError.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第 " + (k + 1) + " 列不能为空");
                                    } else {
                                        if (Constant.DOUBLE.equals(sheets.get(i).getColumn()[k].getType()) || Constant.INTEGER.equals(sheets.get(i).getColumn()[k].getType())) {
                                            String cellValue;
                                            switch (cell.getCellTypeEnum()) {
                                                case STRING:
                                                    cellValue = cell.getStringCellValue().trim().replaceAll(",", "");
                                                    break;
                                                case NUMERIC:
                                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                                    break;
                                                case FORMULA:
                                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                                    break;
                                                default:
                                                    cellValue = cell.toString();
                                                    break;
                                            }
                                            if (cellValue != null && !cellValue.matches("(-)?\\d+(.\\d+)?(E\\d+)?")) {
                                                hasRowError = true;
                                                rowError.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第 " + (k + 1) + " 列 " + cellValue + " 为非法数字");
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        if (hasRowError) {
                            hasSheetError = true;
                            sheetError.append(rowError.toString());
                        }
                        hasRowError = false;
                    }
                }
                if (hasSheetError) {
                    hasError = true;
                    error.append(sheetError.toString());
                }
                hasSheetError = false;
            }
        }
        if (hasError) {
            return Result.error("-1", error.toString());
        } else {
            StringBuilder right = new StringBuilder();
            right.append("**********<span style='color:green;font-weight:bold;'>导入成功</span>**********");
            return Result.ok(right.toString());
        }
    }
}